/***********************************************************************************************************************************
 *    Name: Replication_Alerts.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE DistributionDB;
GO
ALTER VIEW DBAdmin.ReplicationFailures AS
WITH e AS (
	SELECT agent_id
	  FROM dbo.MSDistribution_History
	  WHERE error_id <> 0 AND DATEDIFF(mi,time,CURRENT_TIMESTAMP) <= 60
	  GROUP BY agent_id
	  HAVING 3 <= COUNT(*)	)	-- 50% failure rate
SELECT
	rmd.publisher,
	da.publisher_db,
	da.publication,
	s.name AS subscriber,
	da.subscriber_db,
	CASE rmd.status WHEN 1 THEN 'Started' WHEN 2 THEN 'Succeeded' WHEN 3 THEN 'In progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retrying' WHEN 6 THEN 'Failed' END AS status
  FROM dbo.MSDistribution_Agents da
	INNER JOIN dbo.MSReplication_MonitorData rmd ON (da.id = rmd.agent_id)
	INNER JOIN master.sys.servers s ON (da.subscriber_id = s.server_id)
  WHERE da.id IN (SELECT agent_id FROM e);
GO
SELECT * FROM DBAdmin.ReplicationFailures ORDER BY publisher, publisher_db, publication, subscriber;
GO
SELECT DISTINCT subscriber, subscriber_db, status FROM DBAdmin.ReplicationFailures ORDER BY subscriber, subscriber_db;